<?php

require_once './_AddinLibraries/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once './_AddinLibraries/database_driver.php';

function excel_getColIndex($colName) {
    $upper = strtoupper($colName);
    return ord($upper) - 65;
}

function excel_set_properties($objPHPExcel) {

    // Set properties
    $objPHPExcel->getProperties()->setCreator("AAAWebsite")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
//$objPHPExcel->getActiveSheet()->getStyle('A1:Z100')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
}

function excel_write_and_redirect($objPHPExcel, $filename) {
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

function pdf_write_and_redirect($objPHPExcel, $filename) {
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment;filename="01simple.pdf"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
    $objWriter->save('php://output');
    exit;
}

function excel_write_header($objPHPExcel, $headers) {
    $col = 0;
    $row = 1;
    foreach ($headers as $header) {
        $objPHPExcel->getActiveSheet()
                ->setCellValueByColumnAndRow($col++, $row, $header);
    }
}

function excel_write_data($objPHPExcel, $data) {
    $col = 0;
    $row = 1;
    foreach ($data as $rowdata) {
        $col = 0;
        ++$row;
        foreach ($rowdata as $cell_data) {
            $objPHPExcel->getActiveSheet()
                    ->setCellValueByColumnAndRow($col++, $row, $cell_data);
        }
    }
}

function excel_read($objPHPExcel, &$header, &$data)
{
    $col = 0;
    $row = 1;
    while( ($celldata= $objPHPExcel->getActiveSheet()
            ->getCellByColumnAndRow($col,$row)->getFormattedValue())!='')
    {
        $header[$col++] = $celldata;
    }

    while(true)
    {
        $row = $row+1;
        $celldata= $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0,$row)->getFormattedValue();
        if ($celldata=='')
            break;
        for ($i=0; $i<$col;$i++)
        {
            $data[$row-1][]= ($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i,$row)->getFormattedValue());
        }
     }
}

function excel_read_range($file_path, $sheet_index, $startRow, $endRow, $startCol, $endCol) {
    //$file_path: remmeber to have at least 1 dot at the beginning of the path, example: ".\path\to\the\file.ext"

    error_reporting(E_ALL);
    date_default_timezone_set('Europe/London');


    //check for file exsitence
    if (!file_exists($file_path)) {
        exit("Not found file \"$file_path\"");
    }

    //open file
    $objPHPExcel = PHPExcel_IOFactory::load($file_path);

    //StartCol = A -> index 0
    $data = array();
    $startCol = excel_getColIndex($startCol);
    $endCol = excel_getColIndex($endCol);
    $workSheet = $objPHPExcel->getSheet($sheet_index);

    //get list of data
    for ($i = $startRow; $i <= $endRow; ++$i) {
        for ($j = $startCol; $j <= $endCol; ++$j) {
            $data[$i][$j] = $workSheet->getCellByColumnAndRow($j, $i)->getFormattedValue();
        }
    }

    //generate headers
    $headers = array();
    $char = 'A';
    for ($j = $startCol; $j <= $endCol; ++$j) {
        $headers[$j] = $char++;
    }

    $form['sample'] = array(
        '#theme' => 'table',
        '#header' => $headers,
        '#rows' => $data,
    );
    return $form;
}

function excel_generate_template($table) {
    $objPHPExcel = new PHPExcel();
    excel_set_properties($objPHPExcel);
    $objPHPExcel->getActiveSheet()->setTitle('Import - ' . $table);
    $Upper_list = array();
    $list_argument = library_generate_field($table, $Upper_list);
    excel_write_header($objPHPExcel, $Upper_list);
    $objPHPExcel->setActiveSheetIndex(0);
    excel_write_and_redirect($objPHPExcel, 'Import - ' . $table . '.xls');
}

function excel_export($table,$data){
    if ($data==array())
    {
        get_table2($table,$data);
    }        
    $objPHPExcel = new PHPExcel();
    excel_set_properties($objPHPExcel);
    $objPHPExcel->getActiveSheet()->setTitle('List of ' . $table);
    if ($table=='teaching_class_1')
    {
        $Upper_list=array('CourseID', 'TeachingClassID', 'Prerequisite', 'TermID', 'Lecturer', 'Year', 'TAs', 'Type', 'Note',  ' ');
    }
    else
    {
        $Upper_list = array();
        $list_argument = library_generate_field($table, $Upper_list);
    }
    excel_write_header($objPHPExcel, $Upper_list);
    excel_write_data($objPHPExcel, $data);
    $objPHPExcel->setActiveSheetIndex(0);
    excel_write_and_redirect($objPHPExcel, 'List of ' . $table . '.xls');
}
function excel_validate($form, &$form_state) {
    $file = file_save_upload('file', array(
        'file_validate_extensions' => array('xls xlsx'), // Validate extensions.
            ));
    // If the file passed validation:
    if ($file) {
        // Move the file, into the Drupal file system
        if ($file = file_move($file, 'public://')) {
            // Save the file for use in the submit handler.
            $form_state['storage']['file'] = $file;
        } else {
            form_set_error('file', t('Failed to write the uploaded file the site\'s file folder.'));
        }
    } else {
        form_set_error('file', t('No file was uploaded.'));
    }
}
function get_table2($tablename, &$data)
{
    select_itec_database();
    $data = array();
    $sql = db_query("SELECT * FROM ".$tablename);
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        $data[] = $erg;
    };
    select_drupal_database();    
}

function excel_exportStudentListFromTeachingClass($id)
{
    //STUDENT LIST SHEET
    //==prepare data
    $coursename='mycoursename';
    $program='myprogram';
    $lab='10%';
    $bonus='10%';
    $midterm='20%';
    $final='60%';
    $rangecol=20;
    $rangerow=20;
    $student1=array('0812203',"Nguyễn Hưng");
    $student2=array('0812129',"Hoàng Nhật Hải");
    $student=array($student1,$student2);   

    //==open file
    $filepath = ('./_ExcelFiles/studentlist_teachingclass.xls');
    $objPHPExcel = PHPExcel_IOFactory::load($filepath);
    
    $row = 1;
    for ($row = 0; $row<$rangerow; ++$row)
        for ($col = 0; $col<$rangecol; ++$col)
        {
           $celldata = $objPHPExcel->getSheetByName('Student list')->getCellByColumnAndRow($col,$row)->getFormattedValue();
           switch ($celldata){
                case "{trainingprogram}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $program);
                    break;                
                case "{coursename}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $coursename);
                    break;
                case "{lab}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $lab);
                    break;
                case "{midterm}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $midterm);
                    break;
                case "{bonus}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $bonus);
                    break;
                case "{final}":
                    $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row, $final);
                    break;
                case "{studentid}":
                    for ($i=0; $i<sizeof($student);++$i)
                    {
                        $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col, $row+$i, ' '.$student[$i][0]);
                        $objPHPExcel->getSheetByName('Student list')->setCellValueByColumnAndRow($col+1, $row+$i, ' '.$student[$i][1]);
                    }
                    break;
           }
        }
        
    //COURSE INFO SHEET

    //write data
    excel_write_and_redirect($objPHPExcel, 'Student list - '.$id.'.xls');
}